// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data;
using System.Data.Common;

// bugbug - test get tables/views/columns works on Oracle, DB2, & PostgreSQL
// bugbug - get PK:FK, etc.
// useful info at http://www.alberton.info/sql_server_meta_info.html

namespace net.windward.utils.ado.Unknown
{
	/// <summary>
	/// An unknown database.
	/// </summary>
	public class WrUnknownDatabase : IWrDatabase
	{
		private readonly DbProviderFactory providerFactory;
		private readonly string server;
		private readonly string database;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] procedures;

		private static readonly Hashtable types;

		static WrUnknownDatabase()
		{
			types = new Hashtable();
			types.Add("character", DbType.String);

			types.Add("bigint", DbType.Int64);
			types.Add("tinyint", DbType.Byte);
			types.Add("smallint", DbType.Int16);
			types.Add("mediumint", DbType.Int32);
			types.Add("int", DbType.Int32);
			types.Add("integer", DbType.Int32);
			types.Add("float", DbType.Single);
			types.Add("double", DbType.Double);
			types.Add("decimal", DbType.Decimal);
			types.Add("numeric", DbType.Decimal);
			types.Add("bool", DbType.Byte);
			types.Add("boolean", DbType.Byte);
			types.Add("datetime", DbType.DateTime);
			types.Add("timestamp", DbType.DateTime);
			types.Add("time", DbType.Object);
			types.Add("year", DbType.Int32);
			types.Add("char", DbType.String);
			types.Add("nchar", DbType.String);
			types.Add("varchar", DbType.String);
			types.Add("nvarchar", DbType.String);
			types.Add("binary", DbType.String);
			types.Add("varbinary", DbType.String);
			types.Add("blob", DbType.Binary);
			types.Add("tinyblob", DbType.Binary);
			types.Add("mediumblob", DbType.Binary);
			types.Add("longblob", DbType.Binary);
			types.Add("text", DbType.String);
			types.Add("tinytext", DbType.String);
			types.Add("mediumtext", DbType.String);
			types.Add("longtext", DbType.String);
			types.Add("bit", DbType.Binary);
			types.Add("enum", DbType.Object);
			types.Add("set", DbType.Object);

			types.Add("clob", DbType.String);
			types.Add("nvarchar2", DbType.String);
			types.Add("varchar2", DbType.String);
			types.Add("timestamp(6)", DbType.DateTime);
			types.Add("binary_float", DbType.Single);
			types.Add("binary_double", DbType.Double);
		}

		/// <summary>
		/// Create an unknown database object.
		/// </summary>
		/// <param name="providerFactory">The .NET factory for this vendor</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrUnknownDatabase(DbProviderFactory providerFactory, string server, string database)
		{
			this.providerFactory = providerFactory;
			this.server = server;
			this.database = database;
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return null;
			}
		}

		private DbType ConvertFromDbType(string name)
		{

			string key = name.ToLower();
			if (types.Contains(key))
				return (DbType)types[key];
			try
			{
				DbType typ = (DbType)Enum.Parse(typeof(DbType), name, true);
				DbParameter param = providerFactory.CreateParameter();
				if (param != null)
				{
					param.DbType = typ;
					return param.DbType;
				}
				return DbType.Object;
			}
			catch (Exception)
			{
				return DbType.Object;
			}
		}
		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return procedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(GetCommandLine(credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.START, database);

			tables = GetTables(connStr, owner, status);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = GetColumns(tables[index], connStr, owner);
				tables[index] = new WrTable(tables[index].Schema, tables[index].Name, tables[index].Description, tables[index].IsSystem, columns);
			}

			views = GetViews(connStr, owner, status);
			for (int index = 0; index < views.Length; index++)
			{
				WrColumn[] columns = GetColumns(views[index], connStr, owner);
				views[index] = new WrView(views[index].Schema, views[index].Name, views[index].Description, views[index].IsSystem, columns);
			}

			procedures = GetStoredProcedures(connStr, owner, status);
		}

		/// <summary>
		/// Return all tables in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all tables in this database.</returns>
		private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			ArrayList rtn = new ArrayList();
			using (DbConnection conn = providerFactory.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();

				using (DbCommand cmd = providerFactory.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText =
						"SELECT table_name, table_schema FROM INFORMATION_SCHEMA.TABLES WHERE (table_type = 'BASE TABLE') ORDER BY table_name";

					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string tableName = myReader.GetString(0);
							rtn.Add(new WrTable(string.Empty, tableName, "", false, null));
							if (status != null)
								status(WrDatabase.TYPE.TABLE, tableName);
						}
					}
				}
			}
			return (WrTable[])rtn.ToArray(typeof(WrTable));
		}

		/// <summary>
		/// Return all views in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what views are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all views in this database.</returns>
		private WrView[] GetViews(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			ArrayList rtn = new ArrayList();
			using (DbConnection conn = providerFactory.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();

				using (DbCommand cmd = providerFactory.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = "SELECT table_name, table_schema FROM INFORMATION_SCHEMA.TABLES WHERE (table_type = 'VIEW') ORDER BY table_name";

					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string viewName = myReader.GetString(0);
							rtn.Add(new WrView(string.Empty, viewName, "", false, null));
							if (status != null)
								status(WrDatabase.TYPE.VIEW, viewName);
						}
					}
				}
			}
			return (WrView[])rtn.ToArray(typeof(WrView));
		}

		/// <summary>
		/// Return all columns in a table or view.
		/// </summary>
		/// <param name="table">The name of the table or view.</param>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <returns>all columns in a table or view.</returns>
		public WrColumn[] GetColumns(WrBaseTable table, string connStr, WrVendor.ELEM_OWNER owner)
		{

			// name & type
			ArrayList columns = new ArrayList();
			using (DbConnection conn = providerFactory.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();

				// get the FK:PK relationships
				Hashtable pkMaps = new Hashtable();
				if ((owner & WrVendor.ELEM_OWNER.KEY_JOINS) != 0)
					try
					{
						using (DbCommand cmd = providerFactory.CreateCommand())
						{
							cmd.Connection = conn;
							cmd.CommandType = CommandType.Text;
							cmd.CommandText =
								"SELECT k.COLUMN_NAME, ccu.TABLE_NAME AS 'references_table', ccu.COLUMN_NAME AS 'references_field' " +
								"FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k LEFT OUTER JOIN " +
								"INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c ON k.TABLE_NAME = c.TABLE_NAME AND k.TABLE_SCHEMA = c.TABLE_SCHEMA AND " +
								"k.TABLE_CATALOG = c.TABLE_CATALOG AND k.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND " +
								"k.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
								"INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON rc.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND " +
								"rc.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
								"INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON rc.UNIQUE_CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA AND " +
								"rc.UNIQUE_CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME " +
								"WHERE(k.CONSTRAINT_CATALOG = DB_NAME()) AND (k.TABLE_NAME = '" + table.Name + "') AND (c.CONSTRAINT_TYPE = 'FOREIGN KEY')";

							using (DbDataReader myReader = cmd.ExecuteReader())
							{
								while (myReader.Read())
								{
									string column = myReader.GetString(0).ToLower().Trim();
									string pk = myReader.GetString(1).Trim() + '.' + myReader.GetString(2).Trim();
									if (!string.IsNullOrEmpty(column))
										pkMaps.Add(column, pk);
								}
							}
						}
					}
					catch (Exception)
					{
						// nada
					}


				using (DbCommand cmd = providerFactory.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_name='" + table.Name + "'";

					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string columnName = myReader.GetString(0);
							DbType typ = ConvertFromDbType(myReader.GetString(1));
							string pk = (string)pkMaps[columnName.ToLower()];
							columns.Add(new WrColumn(columnName, "", typ, pk));
						}
					}
				}
			}
			return (WrColumn[])columns.ToArray(typeof(WrColumn));
		}

		/// <summary>
		/// Return all stored procedures in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what stored procedures are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all stored procedures in this database.</returns>
		private WrStoredProcedure[] GetStoredProcedures(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			ArrayList rtn = new ArrayList();
			using (DbConnection conn = providerFactory.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();
				using (DbCommand cmd = providerFactory.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = "SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = N'PROCEDURE' order by routine_name";

					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string name = myReader.GetString(0);
							rtn.Add(new WrStoredProcedure(null, name, "", false, null));
							if (status != null)
								status(WrDatabase.TYPE.PROCEDURE, name);
						}
					}
				}
			}
			return (WrStoredProcedure[])rtn.ToArray(typeof(WrStoredProcedure));
		}

		private string GetCommandLine(WrCredentials credentials)
		{
			return WrUnknownVendor.ConnectionString(providerFactory, server, database, credentials, true);
		}

	}
}
